<?php

/**
 * Executes query with WHERE and ORDER phrases from $where_and_order, inserting parameters $params, offsetting and limiting with $limit and $offset.
 * When SCOTTLIB_RETURN_SQL is true, returns the DQL rather than executing it.
 * @param string $where_and_order
 * @param Array $params
 * @param int $limit
 * @param int $offset
 * @return When records are found, an Array of instantiated and populated instance of your object; otherwise, an empty Array. This, unless
 * SCOTTLIB_RETURN_SQL, in which case the DQL is returned instead.
 */
final function select($where_and_order, $params = array(), $limit = 10, $offset = 0) {
	$return = SCOTTLIB_RETURN_SQL;
	
	$class = $this->getClass();
	
	$subqueries = array();
	$subqueries[] = '*';
	
	foreach($this->getProperties() as $prop) {
		if (get_class($prop) == 'ReflectionProperty') {
			if ($q = ReflectionUtils::getPropertyAnnotation($this, $prop->getName(), 'subquery')) {
				$prepared = slMySqlConn::prepare($q, array('tableName' => $this->getTableName()));
				$sql = slMySqlConn::prepare("( %p ) AS #", array($prepared, $prop->getName()));
				$subqueries[] = $sql; 
			}
		}
	}
	
	$sql = slMySqlConn::prepare("SELECT %p FROM # %p LIMIT %d OFFSET %d", array(join(',', $subqueries), $this->_table, slMySqlConn::prepare($where_and_order, $params), $limit, $offset));
	
	if ($return)
		return $sql;
	else {
		$objs = array();
		foreach($this->_conn->select($sql)->assocs() as $record) {
			$o = $class->newInstance();
			$o->bind($record);
			$objs[] = $o;
		}
		
		return $objs;
	}
}

final function selectOne($where_and_order, $params = array()) {
	$return = SCOTTLIB_RETURN_SQL;
	$result = $this->select($where_and_order, $params, 1, 0, $return);
	if ($return)
		return $result; // sql
	else
		return (count($result) ? $result[0] : null);
}

final function getAll($order = '', $limit=250, $return = false) {
	return $this->select($order, array(), $limit, 0, $return);
}

final function delete($return = false) {
	if (!($id = $this->getId()) && !$return)
		throw new Exception("Cannot delete record: it isn't connected to the database.");
		
	$this->onBeforeDelete($id);
	$sql = slMySqlConn::prepare("DELETE FROM # WHERE #=%s", array($this->_table, $this->_idProperty, $id));
	if ($return)
		return $sql;
	else {
		$affected = $this->_conn->delete($sql);
		$this->onAfterDelete($id);
		return $affected;
	}
}

function deleteAll($return = false) {
	$sql = slMySqlConn::prepare("DELETE FROM #", $this->_table);
	return ($return ? $sql : $this->_conn->execute($sql)); 		
}
